package com.jdbc;


import com.mchange.v2.c3p0.ComboPooledDataSource;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.beans.PropertyVetoException;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * Created by pullFoot_youth on 2017/2/25.
 */
public class Jdbctest {


    @Test
    public void testDataSource() {

        /**
         *  use mysqlDataSource to get connection
         *  use spring jdbcTemplate to operate dataBase(usually not use this, with mybatis instead of this)
         */
        MysqlDataSource datasource = new MysqlDataSource();

        datasource.setDatabaseName("test");
        datasource.setURL("jdbc:mysql://localhost:3306/test?serverTimezone=UTC");
        datasource.setPassword("root");
        datasource.setUser("root");
//        datasource.setServerName();
        JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);

        List<Object> lists = jdbcTemplate.query("select * from tb_user where id = ?", new BeanPropertyRowMapper(Object.class), 8);

        System.out.println(lists.get(0).toString());
//        System.out.println(user.toString());


//        try {
//            Connection connection = datasource.getConnection("root","root");
//
//
//
//            System.out.println(connection.hashCode());
//
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }

    }

    @Test
    public void test() {

        try {

            /**
             * use @DriverManager to get connection with dataBase
             *
             */
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?userSSL=false&serverTimezone=UTC&characterEncoding=utf-8", "root", "root");
            String sql = "select * from tb_user where id = '8' ";


            Statement statement = connection.createStatement();

            PreparedStatement ps = connection.prepareStatement("select * from tb_user where id = ?");

            ResultSet rs2 = statement.executeQuery(sql);

            ps.setString(1, "1");
            ResultSet rs = ps.executeQuery();

            while (rs2.next()) {
                String id = rs2.getString(1);
                String name = rs2.getString("name");
                String age = rs2.getString("age");
                String time = rs2.getString("time");
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                java.util.Date t = sdf.parse(time);
                System.out.println(id + name + age + t);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }


    }


    @Test
    public void c3p0DataSoucrTest() {

        /**
         * use c3p0 dataBase pool to get connection
         */
        ComboPooledDataSource ds = new ComboPooledDataSource();
        try {
            ds.setDriverClass("com.mysql.jdbc.Driver");
            ds.setUser("root");
            ds.setPassword("root");
            ds.setJdbcUrl("jdbc:mysql://localhost:3306/test?useSSL=false");
            ds.setInitialPoolSize(20);
            ds.setMaxIdleTime(0);
            ds.setTestConnectionOnCheckin(false);
            ds.setAutomaticTestTable("TestTable");

            Connection connection = ds.getConnection();

            String sql = "select * from tb_user where id = 8";

            PreparedStatement ps = connection.prepareStatement(sql);

            ResultSet rs = ps.executeQuery();

            while(rs.next()){

                System.out.println(rs.getString("id")+" "+rs.getString("name"));
            }

//            JdbcTemplate jt = new JdbcTemplate(ds);
//
//            List<User> u = jt.query("select * from tb_user where id = ?", new BeanPropertyRowMapper<>(User.class), 8);
//
//            System.out.println(u.get(0).toString());

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }


    }

}
